package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import Util.JdbcUtil;
import Entity.*;

public class User_List_Dao {
//显示所有用户信息 查询表user
	public List<User_List_Entity> select(){
		List<User_List_Entity>list = new ArrayList<User_List_Entity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from user;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				User_List_Entity users = new User_List_Entity();
				users.setId(rs.getInt(1));
				users.setName(rs.getString(2));
				users.setSurplus(rs.getDouble(3));
				users.setCode(rs.getInt(4));
				users.setShowd(rs.getInt(5));
				list.add(users);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
//	显示所有showd=0的用户表
	public List<User_List_Entity> select_showd(){
		List<User_List_Entity>list = new ArrayList<User_List_Entity>();
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "select * from user where showd=0;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				User_List_Entity users = new User_List_Entity();
				users.setId(rs.getInt(1));
				users.setName(rs.getString(2));
				users.setSurplus(rs.getDouble(3));
				users.setCode(rs.getInt(4));
				list.add(users);
			}
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
//	修改账户余额
	public void add_user_surplus(double surplus,int code){
		try {
			Connection conn = JdbcUtil.getConnection();
//			update user set surplus=1001 where code = 1;
			String sql = "update user set surplus=" + surplus + " where code = " + code + ";";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
//	修改账户余额
	public void reduce_user_surplus(double surplus,int code){
		try {
			Connection conn = JdbcUtil.getConnection();
//			update user set surplus=1001 where code = 1;
			String sql = "update user set surplus=" + surplus + " where code = " + code + ";";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
//	修改账户的所有信息
	public void update(User_List_Entity user){
		try {
			Connection conn = JdbcUtil.getConnection();
//			update user set name='赵',surplus='1124',code='1' where id=1;
			String sql = "update user set name='" + user.getName() + "',surplus='" + user.getSurplus() + "',code='" + user.getCode() + "' where id="+ user.getId() +";";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
//	添加账户
	public void add(User_List_Entity user){
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "insert into user(name,surplus,code,showd) values('"+user.getName()+"','"+user.getSurplus()+"','"+user.getCode()+"',0);";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
//	直接删除
	public void delete(int id){
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "delete from user where id = "+ id +";";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
//	设账户为不可见，即 冻结账户 在信息管理、增加票据页面中不显示，在日记账中可查
	public void update_delete(int id){
		try {
			Connection conn = JdbcUtil.getConnection();
			String sql = "update user set showd=1 where id="+id+";";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			JdbcUtil.close(pstmt, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
//	恢复账户为可见，即 解冻账户
	public void update_undelete(int id){
		try{
			Connection conn = JdbcUtil.getConnection();
			String sql = "update user set showd=0 where id="+id+";";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.executeUpdate();
			JdbcUtil.close(pstmt, conn);
		}catch (SQLException e){
			e.printStackTrace();
		}
	}
}
